package dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import entity.Book;
import entity.Order;
import entity.OrderItem;
import util.DbUtil;
public class BookDao {

	public Book findProductById(String id) throws SQLException {
		// TODO Auto-generated method stub	
		Book p=null;
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		ResultSetHandler<Book> h = new BeanHandler<Book>(Book.class);
		String sql="select * from book where id=?";
		p=run.query(conn, sql,h,id);
		return p;
		
	}
	@SuppressWarnings("unchecked")
	public int findAllCount(String category) throws SQLException {
		String sql = "select count(*) from book";
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();		
		if (!"全部商品".equals(category)) {
			sql += " where category=?";

			Long count = (Long) run.query(conn,sql, new ScalarHandler(), category);
			return count.intValue();
		} else {
			Long count = (Long) run.query(conn,sql, new ScalarHandler());

			return count.intValue();
		}
	}
	
	public List<Book> findByPage(int currentPage, int currentCount, String category) throws SQLException {
		// 要执行的sql语句
				String sql = null;
				// 参数
				Object[] obj = null;
				QueryRunner run = new QueryRunner();		
				Connection conn = DbUtil.getConn();
				// 如果category不为null,代表是按分类查找
				if (!"全部商品".equals(category)) {
					sql = "select * from book  where category=? limit ?,?";
					obj = new Object[] { category, (currentPage - 1) * currentCount,
							currentCount, };
				} else {
					sql = "select * from book  limit ?,?";
					obj = new Object[] { (currentPage - 1) * currentCount,
							currentCount, };
				}
				return run.query(conn,sql, new BeanListHandler<Book>(Book.class),
						obj);
	}
	public void changProductNum(Order order) throws SQLException {
		// 修改商品的库存数量
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		String sql="update book set pnum=pnum-? where  id=?";
		//获取订单条目
		List<OrderItem> items=order.getOrderItems();
		for (OrderItem b:items) {
			String id=b.getP().getId();
			int item_num=b.getBuynum();
			run.update(conn,sql,item_num,id);
		}
		
		
	}
	public List<Book> findBookByName(int currentPage, int currentCount,
			String searchfield) throws SQLException {
		//根据名字模糊查询图书
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		String sql = "select * from book where name like '%"+searchfield+"%' limit ?,?";	
		return run.query(conn,sql, new BeanListHandler<Book>(Book.class),currentPage-1,currentCount);
	}

	//前台搜索框，根据书名模糊查询出的图书总数量
	public int findBookByNameAllCount(String searchfield) throws SQLException {
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		String sql = "select count(*) from book where name like '%"+searchfield+"%'";	
		//查询出满足条件的总数量，为long类型
		Long count = (Long)run.query(conn,sql, new ScalarHandler());
		return count.intValue();
	}
	//查询出热卖产品
	public List<Object[]> getWeekHotProduct() throws SQLException {
		QueryRunner run = new QueryRunner();		
		Connection conn = DbUtil.getConn();
		String sql = "SELECT book.id,book.name, "+
                " book.imgurl,SUM(orderitem.buynum) totalsalnum "+
        " FROM orderitem,orders,book "+
        " WHERE orderitem.order_id = orders.id "+
                " AND book.id = orderitem.book_id "+
                " AND orders.paystate=1 "+
                " AND orders.ordertime > DATE_SUB(NOW(), INTERVAL 7 DAY) "+
        " GROUP BY book.id,book.name,book.imgurl "+
        " ORDER BY totalsalnum DESC "+
        " LIMIT 0,2 ";
		return run.query(conn,sql, new ArrayListHandler());
	}
	// 查找所有商品
		public List<Book> listAll() throws SQLException {
			String sql = "select * from book";
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();
			return run.query(conn,sql, new BeanListHandler<Book>(Book.class));
		}
	//多条件查询	
		public List<Book> findProductByManyCondition(String id, String name, String category, String minprice,
				String maxprice) throws SQLException {
			List<Object> list = new ArrayList<Object>();
			String sql = "select * from book where 1=1 ";
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();			
			if (id != null && id.trim().length() > 0) {
				sql += " and id=?";
				list.add(id);
			}

			if (name != null && name.trim().length() > 0) {
				sql += " and name=?";
				list.add(name);
			}
			if (category != null && category.trim().length() > 0) {
				sql += " and category=?";
				list.add(category);
			}
			if (minprice != null && maxprice != null
					&& minprice.trim().length() > 0 && maxprice.trim().length() > 0) {
				sql += " and price between ? and ?";
				list.add(minprice);
				list.add(maxprice);
			}
			Object[] params = list.toArray();
			List<Book> list2=run.query(conn,sql, new BeanListHandler<Book>(Book.class),params);
			return list2;
			
		}
		//后台添加商品方法
		public void addProduct(Book p) throws SQLException {
			// TODO Auto-generated method stub
			String sql = "insert into book values(?,?,?,?,?,?,?)";
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();		
			run.update(conn,sql, p.getId(), p.getName(), p.getPrice(),
					p.getCategory(), p.getPnum(), p.getImgurl(), p.getDescription());
		}
		//后台编辑商品方法
		public void editProduct(Book p) throws SQLException {
			//.创建sql语句，并拼接sql
			String sql  = "update book " +
					      "set  name=?,price=?,category=?,pnum=?,description=? ";
			//把商品添加到集合中
			List<Object> obj = new ArrayList<Object>();
			obj.add(p.getName());
			obj.add(p.getPrice());
			obj.add(p.getCategory());
			obj.add(p.getPnum());
			obj.add(p.getDescription());
			//判断是否有图片		
			if (p.getImgurl() != null && p.getImgurl().trim().length() > 0) {
				sql += " ,imgurl=?";
				obj.add(p.getImgurl());
			}
			sql += " where id=?";
			obj.add(p.getId());		
			System.out.println(sql);		
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();		
			//4.使用QueryRunner对象的update()方法更新数据
			run.update(conn,sql, obj.toArray());
		}
		//后台删除商品方法
		public void deleteProduct(String id) throws SQLException {
			// TODO Auto-generated method stub
			String sql = "delete from book where id = ?";
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();	
			run.update(conn,sql, id);
		}
		//查询已经支付的商品信息
		public List<Object[]> download(String year, String month) throws SQLException {
			// TODO Auto-generated method stub
			String sql = "select book.name,sum(orderitem.buynum) totalsalnum from orders,book,orderItem "
					+ "where orders.id=orderItem.order_id and book.id=orderItem.book_id "
					+ "and orders.paystate=1 and year(ordertime)=? and month(ordertime)=? "
					+ "group by book.name "
					+ "order by totalsalnum desc";
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();	
			List<Object[]> list=run.query(conn,sql, new ArrayListHandler(),year,month);
			return list;
		}
		/*
		 * 删除订单，修改商品数量
		 */
		public void updateProductNum(List<OrderItem> items) throws SQLException {
			// TODO Auto-generated method stub
			String sql = "update book set pnum=pnum+? where id=?";
			QueryRunner run = new QueryRunner();		
			Connection conn = DbUtil.getConn();		
			Object[][] params = new Object[items.size()][2];

			for (int i = 0; i < params.length; i++) {
				params[i][0] = items.get(i).getBuynum();
				params[i][1] = items.get(i).getP().getId();
			}

			run.batch(conn, sql, params);
		}
		

}
